package com.mi.sql

import org.apache.spark.sql.SparkSession


object patient {
  def main(args: Array[String]): Unit = {
    val spark = SparkSession.builder().appName("MedicalInsurance").master("local").getOrCreate()
    val data = spark.read
      .option("header", "true")
      .csv("data/data.csv")
    val ownExpenseDF = data
      .select("药品费自费金额_SUM", "检查费自费金额_SUM", "治疗费自费金额_SUM", "手术费自费金额_SUM", "医用材料费自费金额_SUM")
      .toDF()
    ownExpenseDF.createTempView("own_expense")

    val declaredExpenseDF = data
      .select("药品费申报金额_SUM", "检查费申报金额_SUM", "治疗费申报金额_SUM", "手术费申报金额_SUM", "床位费申报金额_SUM", "成分输血申报金额_SUM", "一次性医用材料申报金额_SUM", "其它申报金额_SUM")
      .toDF()
    declaredExpenseDF.createTempView("declared_expense")

    // 各项平均自费金额
    spark.sql(
      """
        SELECT
        AVG(`药品费自费金额_SUM`) AS `平均药品费自费金额`,
        AVG(`检查费自费金额_SUM`) AS `平均检查费自费金额`,
        AVG(`治疗费自费金额_SUM`) AS `平均治疗费自费金额`,
        AVG(`手术费自费金额_SUM`) AS `平均手术费自费金额`,
        AVG(`医用材料费自费金额_SUM`) AS `平均医用材料费自费金额`
        FROM `own_expense`
      """).show()

    // 平均自费金额中，各项的占比
    spark.sql(
      """
        SELECT
        AVG(`药品费自费金额_SUM`)+AVG(`检查费自费金额_SUM`)+AVG(`治疗费自费金额_SUM`)+AVG(`手术费自费金额_SUM`)+AVG(`医用材料费自费金额_SUM`) AS sum_amount,
        AVG(`药品费自费金额_SUM`) / sum_amount AS `平均药品费自费金额占比`,
        AVG(`检查费自费金额_SUM`) / sum_amount AS `平均检查费自费金额占比`,
        AVG(`治疗费自费金额_SUM`) / sum_amount AS `平均治疗费自费金额占比`,
        AVG(`手术费自费金额_SUM`) / sum_amount AS `平均手术费自费金额占比`,
        AVG(`医用材料费自费金额_SUM`) / sum_amount AS `平均医用材料费自费金额占比`
        FROM `own_expense`
      """).show()

    // 各项平均申报金额
    spark.sql(
      """
        SELECT
        AVG(`药品费申报金额_SUM`) AS `平均药品费申报金额`,
        AVG(`检查费申报金额_SUM`) AS `平均检查费申报金额`,
        AVG(`治疗费申报金额_SUM`) AS `平均治疗费申报金额`,
        AVG(`手术费申报金额_SUM`) AS `平均手术费申报金额`,
        AVG(`床位费申报金额_SUM`) AS `平均床位费申报金额`,
        AVG(`成分输血申报金额_SUM`) AS `平均成分输血申报金额`,
        AVG(`一次性医用材料申报金额_SUM`) AS `平均一次性医用材料申报金额`,
        AVG(`其它申报金额_SUM`) AS `其它申报金额`
        FROM `declared_expense`
      """).show()

    // 平均申报金额中，各项的占比
    spark.sql(
      """
        SELECT
        AVG(`药品费申报金额_SUM`)+AVG(`检查费申报金额_SUM`)+AVG(`治疗费申报金额_SUM`)+AVG(`手术费申报金额_SUM`)+AVG(`床位费申报金额_SUM`)+AVG(`成分输血申报金额_SUM`)+AVG(`一次性医用材料申报金额_SUM`)+AVG(`其它申报金额_SUM`) AS sum_amount,
        AVG(`药品费申报金额_SUM`) / sum_amount AS `平均药品费申报金额占比`,
        AVG(`检查费申报金额_SUM`) / sum_amount AS `平均检查费申报金额占比`,
        AVG(`治疗费申报金额_SUM`) / sum_amount AS `平均治疗费申报金额占比`,
        AVG(`手术费申报金额_SUM`) / sum_amount AS `平均手术费申报金额占比`,
        AVG(`床位费申报金额_SUM`) / sum_amount AS `平均床位费申报金额占比`,
        AVG(`成分输血申报金额_SUM`) / sum_amount AS `平均成分输血申报金额占比`,
        AVG(`一次性医用材料申报金额_SUM`) / sum_amount AS `平均一次性医用材料申报金额占比`,
        AVG(`其它申报金额_SUM`) / sum_amount AS `其它申报金额占比`
        FROM `declared_expense`
      """).show()

    spark.stop()
  }
}
